﻿using NetTopologySuite.Features;
using NetTopologySuite.Geometries;
using NetTopologySuite.IO;
using Newtonsoft.Json;
using Npgsql;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace IndoorPosition.Server.Core.Utils
{
    /// <summary>
    /// POSTGIS空间数据库操作类
    /// </summary>
    public class PostGISUtil:IDisposable
    {

        NpgsqlConnection _connetction;
        string _shschema;

        public PostGISUtil(string host, int port, string database, string user, string password, string shschema = "public")
        {
            _shschema = shschema;
            var connString = "Host=" + host + ";Port=" +port + ";Username=" + user + ";Password=" + password + ";Database=" + database + "";
            NpgsqlConnection.GlobalTypeMapper.UseNetTopologySuite();
            _connetction = new NpgsqlConnection(connString);
            _connetction.Open();
            _connetction.TypeMapper.UseNetTopologySuite();
        }

        public void Dispose()
        {
            _connetction.Close();
        }

        /// <summary>
        /// 保存数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="geojson"></param>
        /// <exception cref="Exception"></exception>
        public FeatureClassMeta Save(string tableName, string geojson)
        {
            Feature.ComputeBoundingBoxWhenItIsMissing = true;
            var serializer = GeoJsonSerializer.Create();
            FeatureCollection features;
            using (var stringReader = new StringReader(geojson))
            {
                using (var jsonReader = new JsonTextReader(stringReader))
                {
                    features = serializer.Deserialize<FeatureCollection>(jsonReader);
                }
            }

            if (features == null || features.Count == 0)
            {
                throw new Exception("上传的数据没有要素");
            }

            if (TableExist(tableName))
            {
                throw new Exception("数据库中已存在同名的数据表");
            }

            var firstFeature = features.FirstOrDefault();
            var attributes = firstFeature.Attributes;
            var fields = CreateTable(tableName, attributes, firstFeature.Geometry.OgcGeometryType.ToString(), firstFeature.Geometry.SRID);

            var sql1 = "INSERT INTO " + _shschema + "." + tableName + " (";
            var sql2 = " VALUES (";
            for (int i = 0; i < fields.Count; i++)
            {
                var field = fields[i];
                sql1 += field + ",";
                sql2 += "@" + field + ",";
            }

            sql1 += "geom)";
            sql2 += "@geom)";

            var sql = sql1 + sql2;
            foreach (var feature in features)
            {
                using (var cmd = new NpgsqlCommand(sql, _connetction))
                {
                    foreach (var field in fields)
                    {
                        var value = feature.Attributes.GetOptionalValue(field);
                        if (value == null)
                        {
                            switch (attributes.GetType(field).Name)
                            {
                                case "Int32":
                                case "Int64":
                                    value = 0;
                                    break;
                                case "Boolean":
                                    value = true;
                                    break;
                                case "DateTime":
                                    value = DateTime.Now;
                                    break;
                                case "Double":
                                    value = 0;
                                    break;
                                case "String":
                                    value = string.Empty;
                                    break;
                                default:
                                    break;
                            }
                        }
                        cmd.Parameters.AddWithValue("@" + field, value);
                    }
                    cmd.Parameters.AddWithValue("@geom", feature.Geometry);
                    cmd.ExecuteNonQuery();
                }
            }

            var meta = new FeatureClassMeta()
            {
                SRID = firstFeature.Geometry.SRID,
                Bounding = features.BoundingBox,
                Attributes = GetGSAttribute(attributes, firstFeature.Geometry.OgcGeometryType.ToString())
            };

            return meta;
        }

        /// <summary>
        /// 表是否存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public bool TableExist(string tableName)
        {
            var sql = "select count(*) from information_schema.tables where table_schema='" + _shschema + "' and table_type='BASE TABLE' and table_name='" + tableName + "'";
            using (var cmd = new NpgsqlCommand(sql, _connetction))
            {
                using (var reader = cmd.ExecuteReader())
                {

                    if (reader.Read())
                    {
                        var count = reader.GetInt32(0);
                        if (count > 0)
                        {
                            _connetction.CloseAsync();
                            return true;
                        }
                    }
                }
            }
            return false;
        }

        /// <summary>
        /// 删除表
        /// </summary>
        /// <param name="tableName"></param>
        public void DeleteTable(string tableName)
        {
            var sql = "DROP TABLE " + _shschema + "." + tableName;
            using (var cmd = new NpgsqlCommand(sql, _connetction))
            {
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="attributes"></param>
        /// <param name="geometryType"></param>
        public List<string> CreateTable(string tableName, IAttributesTable attributes,string geometryType,int srid)
        {
            var sql = "CREATE TABLE IF NOT EXISTS " + _shschema + "." + tableName + "(";
            List<string> fields = new List<string>();
            for (int i = 0; i < attributes.Count; i++)
            {
                var name = attributes.GetNames()[i];
                var type = attributes.GetType(name);
                switch (type.Name)
                {
                    case "Int32":
                        fields.Add(name);
                        sql += name + " integer,";
                        break;
                    case "Int64":
                        fields.Add(name);
                        sql += name + " bigint,";
                        break;
                    case "Boolean":
                        sql += name + " boolean,";
                        fields.Add(name);
                        break;
                    case "DateTime":
                        sql += name + " time without time zone,";
                        fields.Add(name);
                        break;
                    case "Double":
                        sql += name + " double precision,";
                        fields.Add(name);
                        break;
                    case "String":
                        var length = attributes.GetValues()[i].ToString().Length + 50;
                        sql += name + " character varying(" + length + "),";
                        fields.Add(name);
                        break;
                    default:
                        continue;
                }
            }
            sql += " geom geometry(" + geometryType + "," + srid + "))";

            using (var cmd = new NpgsqlCommand(sql, _connetction))
            {
                cmd.ExecuteNonQuery();
            }
            return fields;
        }

        /// <summary>
        /// 获取GeoServer发布时需要的属性
        /// </summary>
        /// <param name="attributes"></param>
        /// <param name="geometryType"></param>
        /// <returns></returns>
        private List<GSAttribute> GetGSAttribute(IAttributesTable attributes, string geometryType)
        { 
            var list = new List<GSAttribute>();

            for (int i = 0; i < attributes.Count; i++)
            {
                var name = attributes.GetNames()[i];
                var type = attributes.GetType(name);
                var attribute = new GSAttribute()
                {
                    Name = name,
                };
                switch (type.Name)
                {
                    case "Int32":
                        attribute.Binding = "java.lang.Integer";
                        break;
                    case "Int64":
                        attribute.Binding = "java.lang.Long";
                        break;
                    case "Boolean":
                        attribute.Binding = "java.lang.Boolean";
                        break;
                    case "DateTime":
                        attribute.Binding = "java.lang.Time";
                        break;
                    case "Double":
                        attribute.Binding = "java.lang.Double";
                        break;
                    case "String":
                        attribute.Binding = "java.lang.String";
                        break;
                    default:
                        continue;
                }
                list.Add(attribute);
            }

            switch (geometryType)
            {
                case "Point":
                    list.Add(new GSAttribute() { Name = "geom", Binding = "org.locationtech.jts.geom.Point" });
                    break;
                case "LineString":
                case "MultiLineString":
                    list.Add(new GSAttribute() { Name = "geom", Binding = "org.locationtech.jts.geom.LineString" });
                    break;
                case "Polygon":
                case "MultiPolygon":
                    list.Add(new GSAttribute() { Name = "geom", Binding = "org.locationtech.jts.geom.Polygon" });
                    break;
                default:
                    break;
            }
            return list;
        }
    }

    /// <summary>
    /// 数据元数据，用于GeoServer发布
    /// </summary>
    public class FeatureClassMeta
    {
        /// <summary>
        /// 坐标系EPSG代码
        /// </summary>
        public int SRID
        {
            get;
            set;
        }

        /// <summary>
        /// 坐标系标准字符串
        /// </summary>
        public string SRString
        {
            get;
            set;
        }

        /// <summary>
        /// 数据边界
        /// </summary>
        public Envelope Bounding
        {
            get;
            set;
        }

        /// <summary>
        /// 属性
        /// </summary>
        public List<GSAttribute> Attributes
        {
            get;
            set;
        }
    }

    /// <summary>
    /// geoservr发布时用的属性
    /// </summary>
    public class GSAttribute
    { 
        public string Name
        {
            get;
            set;
        }

        public string Binding
        {
            get;
            set;
        }

    }
}
